let db =require('../db');

//获取设备列表
exports.equipmentList = async function (villageId,buildingId) {
 let sql=`
 SELECT 
 eq.id,
 eq.mac,
 eq.ip,
 eq.villageId,
 eq.buildingId,
 eq.unitId,
 eq.SerialNum,
 eq.isonline,
 DATE_FORMAT(eq.createdAt,'%Y-%m-%d %H:%i') AS createdAt,
 vi.name AS villageName,
 bu.name AS buildingName,
 un.name AS unitName
 FROM
 
 (SELECT *FROM equipment WHERE isdelete ='n') AS eq
 LEFT JOIN village AS vi ON vi.id = eq.villageId
 LEFT JOIN building AS bu ON bu.id =eq.buildingId
 LEFT JOIN unit AS un ON un.id =eq.unitId  


 `   
 let where = '';

 if(villageId && villageId!=0){
    where+=` AND eq.villageId =:villageId`
 }
 if(buildingId){
    where+=` AND eq.buildingId =:buildingId`
 }

 sql += where.replace('AND', 'WHERE');
  sql+=` ORDER By eq.createdAt DESC  `
 let data = await db.query(sql,{villageId,buildingId});
 return data[0];
}

//根据villageId 获取所对应的楼宇

exports.getBuildingByvillageId = async function(villageId){

    let sql=`
    SELECT *FROM building  WHERE isdelete='n' AND  villageId=:villageId
    `

    let data  =await db.query(sql,{villageId});
    return data[0];
}


//根据buildingId 获取所对应的的单元

exports.getUnitByBuildingId  = async function(buildingId){
    let sql=`
    SELECT *FROM  unit  WHERE isdelete='n' AND  buildingId=:buildingId
    `
    let data = await db.query(sql,{buildingId});
    return data[0];
}

//验证mac 地址的唯一性

exports.valicationMac = async function(mac){
let sql=`
SELECT *FROM  equipment  WHERE  mac=:mac

`
let data = await db.query(sql,{mac});
return data[0];
}

//添加设备

exports.addEquipment = async function(villageId,buildingId,unitId,SerialNum,mac){
    let sql=`
    INSERT INTO equipment(villageId,buildingId,unitId,mac,SerialNum)
    VALUES(:villageId,:buildingId,:unitId,:mac,:SerialNum)
    `
    let data = await db.query(sql,{villageId,buildingId,unitId,SerialNum,mac});
    return data[0];
}


//验证单元设备唯一性
exports.valicationUnitIsOnly = async function(unitId){
    let sql=`
    SELECT *FROM equipment  WHERE isdelete='n' AND  unitId=:unitId
    `
    let data  =await db.query(sql,{unitId});
    return data[0];
}

//验证设备编号的唯一性
exports.valicationSerialNum  = async function(SerialNum){
  let sql=`
  SELECT *FROM equipment  WHERE isdelete='n' AND  SerialNum=:SerialNum
  `
  let data = await db.query(sql,{SerialNum});
  return data[0];
}

//获取单个设备详情

exports.getEquipmentDetail = async function(equipmentId){
    let sql=`
    SELECT * FROM  equipment  WHERE isdelete='n' AND  id=:equipmentId
    `
    let data = await db.query(sql,{equipmentId});
return data[0][0];
}

//验证修改时单元是否重复

exports.valicationIsEditUnit = async function(id,unitId){
let sql=`
SELECT *FROM  equipment  WHERE isdelete='n' AND  id!=:id AND unitId =:unitId
`

let data=  await db.query(sql,{id,unitId});
return data[0];
}

//设备信息编辑时验证设备编号的唯一性

exports.valicationIsEditSerialNum  = async function(id,SerialNum){
    let sql=`
    SELECT *FROM  equipment  WHERE isdelete='n' AND  id!=:id AND SerialNum =:SerialNum
    `
    let data  = await db.query(sql,{id,SerialNum});
    return data[0];
}
//修改时验证mac 地址 是否唯一

exports.valicationIsEditMac = async function(id,mac){
    let sql=`
    SELECT *FROM  equipment  WHERE isdelete='n' AND  id!=:id AND mac =:mac
    `
    
    let data=  await db.query(sql,{id,mac});
    return data[0];    
}

//修改设备信息

exports.updateEquipment = async function(id,villageId,buildingId,unitId,SerialNum,mac){
    let sql=`
    UPDATE  equipment SET 
    villageId=:villageId,
    buildingId=:buildingId,
    unitId=:unitId,
    SerialNum=:SerialNum,
    mac=:mac
    WHERE id =:id
    
    `
    let data = await db.query(sql,{id,villageId,buildingId,unitId,SerialNum,mac});
    return data[0];
}

//设备删除

exports.delEquipment = async function(equipmentId){
let sql=`
UPDATE  equipment SET isdelete ='y'  where id =:equipmentId
`
let data  =await db.query(sql,{equipmentId});
return data[0];
}

//对通过socket 连接的设备进行信息验证

exports.valicationSocketequipmnet  = async function(mac){
  let sql=`
  SELECT *FROM  equipment  WHERE mac=:mac
  `
  let data  =await db.query(sql,{mac});
  return data[0];


}

//更改图片是否推送信息

exports.updateSynchronous = async function(id){
let sql =`
 UPDATE  family  SET  synchronous ='y' where id =:id
`
let  data  =  await db.query(sql,{id});
return data[0];
}

//设备在线状态记录
exports.updateEquipmentOnlineStatus  = async function(mac,isonline,connectTime){
    let  sql=`
    UPDATE  equipment  SET  isonline =:isonline,
    lastConnectTime =:connectTime
    where mac = :mac
    `
    let  data  =  await db.query(sql,{mac,isonline,connectTime});
    return data[0];
}

//设备下线状态记录

exports.updateEquipmentDisconnectStatus = async  function(mac,isonline,disconnectTime){
    let  sql=`
    UPDATE  equipment  SET  isonline =:isonline,
    lastDisconnectTime =:disconnectTime
    where mac = :mac
    `
    let  data  =  await db.query(sql,{mac,isonline,disconnectTime});
    return data[0];
}